﻿Imports System.Windows.Forms

Public Class Acad_ConverterWizard

    Dim ConvertType As String
    Dim AcadUpin As Integer
    Dim Period As Integer
    Dim PredecessorExist As Boolean = False

    Private Sub CancelBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelBut.Click
        Me.Close()
    End Sub


    Function Validation() As Boolean

        'check if pred exists
        Select Case WizardTab.SelectedIndex
            Case 1
                Validation = PredecessorExist
            Case Else
                Return True
        End Select

    End Function

    Private Sub NextBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextBut.Click


        If WizardTab.SelectedIndex < WizardTab.TabCount - 1 Then
            WizardTab.SelectedIndex = WizardTab.SelectedIndex + 1
            Backbut.Enabled = True
        End If

        If WizardTab.SelectedIndex = WizardTab.TabCount - 1 Then
            NextBut.Enabled = False
        Else
            NextBut.Enabled = True
        End If

        If Not Validation() Then
            NextBut.Enabled = False
        Else
            NextBut.Enabled = True
        End If
    End Sub

    Private Sub Backbut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Backbut.Click
        If WizardTab.SelectedIndex <> 0 Then
            WizardTab.SelectedIndex = WizardTab.SelectedIndex - 1
            NextBut.Enabled = True
        End If

        If WizardTab.SelectedIndex = 0 Then
            Backbut.Enabled = False
        Else
            Backbut.Enabled = True
        End If
    End Sub

    Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged

        If RadioButton1.Checked = False Then
            ConvertType = "Mid"
        Else : ConvertType = "New"
        End If

    


    End Sub

    Private Sub Acad_ConverterWizard_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim sql As String
        Dim TheView As DataView

        WizardTab.DrawMode = System.Windows.Forms.TabDrawMode.OwnerDrawFixed
        WizardTab.ItemSize = New System.Drawing.Size(0, 1)
        WizardTab.SizeMode = System.Windows.Forms.TabSizeMode.Fixed

        'reset form
        WizardTab.SelectedIndex = 0
        Backbut.Enabled = False
        NextBut.Enabled = True
        PredecessorExist = False

        With AdminProvidersFormV2
            AcadUpin = .UPINBox.Text
            sql = "select pp.Pre_UPIN as [UPIN], p.Provider_Name as [Provider Name] from dbo.Predecessor_Providers pp " & _
                    "left outer join dbo.Providers p on pp.Pre_UPIN = p.UPIN " & _
                    "where pp.UPIN = " & AcadUpin
            TheView = LoadDataView(sql)
            DynamicLabel2.Text = AcadUpin
            DynamicLabel1.Text = .NameBox.Text
            AcadDataGridView.DataSource = TheView
        End With

        LoadPeriodBox(PeriodBox)
        PeriodBox.SelectedValue = CurrentPeriodID

        If TheView.Count = 0 Then
            AcadDataGridView.Visible = False
            NoPredecessorLabel.Visible = True
            Label5.Visible = False
            PredecessorExist = False
        ElseIf TheView.Count > 0 Then
            AcadDataGridView.Visible = True
            NoPredecessorLabel.Visible = False
            Label5.Visible = True
            PredecessorExist = True
        End If

    End Sub


    Function UPINString() As String

        Dim r As Integer
        Dim s As String

        s = ""
        For r = 0 To AcadDataGridView.Rows.Count - 1
            If s = "" Then
                s = AcadDataGridView.Item("UPIN", r).Value.ToString
            Else
                s = s & "," & AcadDataGridView.Item("UPIN", r).Value.ToString
            End If
        Next

        UPINString = s
    End Function

    Private Sub WizardTab_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WizardTab.SelectedIndexChanged
        Select Case WizardTab.SelectedIndex
            Case 3
                'do confirmation
                ConfirmLabel.Text = "Academy:  " & DynamicLabel2.Text & " - " & DynamicLabel1.Text & vbLf
                ConfirmLabel.Text = ConfirmLabel.Text & "Predecessor Lead School:  " & AcadDataGridView.Item("UPIN", AcadDataGridView.SelectedRows(0).Index).Value.ToString & " - " & _
                    AcadDataGridView.Item("Provider Name", AcadDataGridView.SelectedRows(0).Index).Value.ToString & vbLf
                ConfirmLabel.Text = ConfirmLabel.Text & "Period:  " & Period_Name(PeriodBox.SelectedValue)

            Case Else

        End Select
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sql As String

        sql = "declare @converttype as char(3), @max as int, @order as int, @period as int, @sql as varchar(1000) " & _
            "declare @fromtable as nchar(25), @totable as nchar(25) " & _
            "declare @fromdatacuttab as table (fromdatacut nchar(37)) " & _
            "declare @fromdatacut as varchar(150), @todatacut as nchar(35), @todatacutdesc as nvarchar(50) " & _
            "declare @schoolupin as varchar(200), @acadupin as int " & _
            "declare @schoolrid as nvarchar(200), @acadrid as nvarchar(11), @columns as varchar(2000) " & _
            "declare @mainschoolupin as int, @mainschoolrid as nvarchar(11) " & _
            "set @period = " & PeriodBox.SelectedValue & _
            "set @schoolupin = '" & UPINString() & "' " & _
            "set @mainschoolupin = " & AcadDataGridView.Item("UPIN", AcadDataGridView.SelectedRows(0).Index).Value & _
            "set @acadupin = " & DynamicLabel2.Text & _
            "set @acadrid = (select rid from dbo.Relationships where upin = @acadupin) " & _
            "set @order = 1  " & _
            "set @max = (select max(Order_ID) from dbo.Converter_Map)+1 " & _
            "if OBJECT_ID('tempdb..#schoolridtab') is not null drop table #schoolridtab " & _
            "create table #schoolridtab (schoolrid nvarchar(13)) " & _
            "set @sql =  " & _
            "'insert into #schoolridtab select distinct char(39)+rid+char(39) from dbo.Relationships where upin in ('+@schoolupin+')' " & _
            "exec(@sql) " & _
            "select @schoolrid = ISNULL(@schoolrid + ',','') + schoolrid from #schoolridtab " & _
            "set @mainschoolrid = (select rid from dbo.Relationships where upin = @mainschoolupin) " & _
            "while @order < @max " & _
            "	begin " & _
            "	set @fromtable = (select distinct From_Table from dbo.Converter_Map where Order_ID = @order and Period_ID = @period) " & _
            "	set @totable = (select distinct To_Table from dbo.Converter_Map where Order_ID = @order and Period_ID = @period) " & _
            "	insert into @fromdatacuttab select char(39)+From_DataCut_ID+char(39) from dbo.Converter_Map where Order_ID = @order and Period_ID = @period " & _
            "	select @fromdatacut = ISNULL(@fromdatacut + ', ','') + fromdatacut from @fromdatacuttab " & _
            "	set @todatacut = (select distinct ToDataCut_ID from dbo.Converter_Map where Order_ID = @order and Period_ID = @period) " & _
            "	set @todatacutdesc = (select distinct ToDataCut_Desc from dbo.Converter_Map where Order_ID = @order and Period_ID = @period and ToDataCut_ID = @todatacut) " & _
            "	if	@fromtable in ('Modelling_Factors','Modelling_Rates') " & _
            "			begin " & _
            "			select @columns = ISNULL(@columns + ', ','') + QUOTENAME(column_name) " & _
            "			from INFORMATION_SCHEMA.COLUMNS " & _
            "			where TABLE_NAME = @fromtable and COLUMN_NAME not in ('RID','DataCut_ID','Source','Status','Description') " & _
            "			order by ORDINAL_POSITION " & _
            "			set @sql =  " & _
            "			' delete from '+@totable+ " & _
            "			' where DataCut_ID = '+char(39)+@todatacut+char(39)+' and RID = '+char(39)+@acadrid+char(39) " & _
            "			exec(@sql) " & _
            "			set @sql =  " & _
            "			'insert into '+@totable+ " & _
            "			' select '+char(39)+@todatacut+char(39)+' as [DataCut_ID], '+char(39)+@acadrid+char(39)+' as [RID], [Source], [Status], '+ " & _
            "			char(39)+@todatacutdesc+char(39)+' as [Description], '+@columns+ " & _
            "			' from '+@fromtable+ " & _
            "			' where [DataCut_ID] in ('+@fromdatacut+') and [RID] = '+char(39)+@mainschoolrid+char(39) " & _
            "			exec(@sql) " & _
            "			end " & _
            "	else " & _
            "			begin " & _
            "			set @sql =  " & _
            "			' delete from '+@totable+ " & _
            "			' where DataCut_ID = '+char(39)+@todatacut+char(39)+' and RID = '+char(39)+@acadrid+char(39) " & _
            "			exec(@sql) " & _
            "			set @sql = " & _
            "			'insert into '+@totable+ " & _
            "			' select '+char(39)+@todatacut+char(39)+' as [DataCut_ID], '+char(39)+@acadrid+char(39)+' as [RID], [Source], [Status], '+ " & _
            "			char(39)+@todatacutdesc+char(39)+' as [Description], sum(Value) as [Value]'+ " & _
            "			' from '+@fromtable+ " & _
            "			' where [DataCut_ID] in ('+@fromdatacut+') and [RID] in ('+@schoolrid+')'+ " & _
            "			' group by [Source], [Status], [Description]' " & _
            "			exec(@sql)	 " & _
            "			end " & _
            "		set @columns = NULL " & _
            "		set @fromdatacut = NULL " & _
            "		set @order = @order + 1 " & _
            "		delete from @fromdatacuttab " & _
            "end " & _
            "drop table #schoolridtab "

        Dim ErrorMsg As String

        ErrorMsg = RunNonQuery(sql)

        If ErrorMsg <> "" Then
            MessageBox.Show(ErrorMsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            MessageBox.Show("The provider has successfully been converted", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Me.Close()
        End If

    End Sub
End Class
